insert overwrite table jms_dwd.dwd_tmsnew_shipment_stop_union_base_hi partition (dt)
select id
     , shipment_id
     , shipment_no
     , sort_num
     , network_id
     , network_code
     , network_name
     , roadsection_id
     , planned_arrival_day
     , planned_arrival_hour
     , planned_arrival_time
     , actual_arrival_time
     , planned_departure_day
     , planned_departure_hour
     , planned_departure_time
     , actual_departure_time
     , stop_time
     , usetime
     , mileage
     , speed
     , batch_time
     , handling_type
     , load_start_time
     , load_end_time
     , unload_start_time
     , unload_end_time
     , app_arrival_time
     , app_departure_time
     , gps_arrival_time
     , gps_departure_time
     , lock_time
     , unlock_time
     , weight_arrival_time
     , weight_departure_time
     , arrival_gross_weight
     , arrival_tare_weight
     , arrival_net_weight
     , departure_gross_weight
     , departure_tare_weight
     , departure_net_weight
     , estimate_arrival_time
     , plan_driving_time
     , actual_driving_time
     , actual_arrival_time_source
     , actual_departure_time_source
     , estimate_batch_time
     , actual_batch_time
     , create_by
     , create_by_name
     , create_time
     , update_by
     , update_by_name
     , update_time
     , simple_name
     , latest_arrival_time
     , latest_arrival_hour
     , latest_arrival_day
     , gps_depart_mileage
     , gps_arrival_mileage
     , is_send
     , null as financial_center_id
     , null as financial_center_code
     , null as financial_center_desc
     , null as clock_time
     , 1    as table_type
     , t2.t_dt
from (
         select *
         from jms_ods.yl_tmsnew_tms_shipment_stop_hf
         where dt = '{{ execution_date | cst_hour }}'
           and substr(create_time, 1, 10) between date_sub(
                 substr('{{ execution_date | cst_hour }}', 1, 10), 100)
             and substr('{{ execution_date | cst_hour }}', 1, 10)
     ) t1
         join
     (
         select shipment_no as s_shipment_no, substr(planned_departure_time, 1, 10) as t_dt
         from jms_ods.yl_tmsnew_tms_shipment_hf
         where dt = '{{ execution_date | cst_hour }}'
         and substr(create_time, 1, 10) between date_sub(substr('{{ execution_date | cst_hour }}', 1, 10), 100)
         and substr('{{ execution_date | cst_hour }}', 1, 10)
         and substr(planned_departure_time, 1, 10) >= '{{ execution_date | date_add(-90) | cst_ds }}'
         and substr(planned_departure_time, 1, 10) <= '{{ execution_date | date_add(7) | cst_ds }}'
     ) t2
     on t1.shipment_no = t2.s_shipment_no
union all
select id
     , shipment_id
     , shipment_no
     , sort_num
     , network_id
     , network_code
     , network_name
     , roadsection_id
     , planned_arrival_day
     , planned_arrival_hour
     , planned_arrival_time
     , actual_arrival_time
     , planned_departure_day
     , planned_departure_hour
     , planned_departure_time
     , actual_departure_time
     , stop_time
     , usetime
     , mileage
     , speed
     , batch_time
     , handling_type
     , load_start_time
     , load_end_time
     , unload_start_time
     , unload_end_time
     , app_arrival_time
     , app_departure_time
     , gps_arrival_time
     , gps_departure_time
     , lock_time
     , unlock_time
     , weight_arrival_time
     , weight_departure_time
     , arrival_gross_weight
     , arrival_tare_weight
     , arrival_net_weight
     , departure_gross_weight
     , departure_tare_weight
     , departure_net_weight
     , estimate_arrival_time
     , plan_driving_time
     , actual_driving_time
     , actual_arrival_time_source
     , actual_departure_time_source
     , estimate_batch_time
     , actual_batch_time
     , create_by
     , create_by_name
     , create_time
     , update_by
     , update_by_name
     , update_time
     , simple_name
     , null as latest_arrival_time
     , null as latest_arrival_hour
     , null as latest_arrival_day
     , gps_depart_mileage
     , gps_arrival_mileage
     , null as is_send
     , financial_center_id
     , financial_center_code
     , financial_center_desc
     , clock_time
     , 2    as table_type
     , t2.t_dt
from (
         select *
         from (
                  select *,
                         row_number()
                                 over (partition by shipment_no,network_code,sort_num order by update_time desc) as rn
                  from jms_ods.yl_tmsnew_branch_shipment_stop_hi
                  where dt between date_sub(substr('{{ execution_date | cst_hour }}', 1, 10), 100)
                            and date_add(substr('{{ execution_date | cst_hour }}', 1, 10), 2)
              ) tt1
         where rn = 1
     ) t1
         join
     (
         select s_shipment_no
              , t_dt
         from (
                  select shipment_no                                                             as s_shipment_no,
                         substr(planned_departure_time, 1, 10)                                   as t_dt,
                         row_number() over (partition by shipment_no order by update_time desc ) as rn
                  from jms_ods.yl_tmsnew_branch_shipment_hi
                  where dt between date_sub(substr('{{ execution_date | cst_hour }}', 1, 10), 100)
                  and date_add(substr('{{ execution_date | cst_hour }}', 1, 10), 2)
                  and substr(planned_departure_time, 1, 10) >= '{{ execution_date | date_add(-90) | cst_ds }}'
                  and substr(planned_departure_time, 1, 10) <= '{{ execution_date | date_add(7) | cst_ds }}'
              ) tt2
         where rn = 1
     ) t2
     on t1.shipment_no = t2.s_shipment_no
     distribute by t_dt;